# -*- coding: utf-8 -*-
#!usr/bin/python
# encoding: utf-8

import xlrd
import xlwt

import re
from xlutils.copy import copy

fname = "score.xls"
fname_Template_md="Template.md"
fname_output_md="Readme.md"

#open excel file，handle is bk
bk = xlrd.open_workbook(fname,formatting_info=True)
shxrange = range(bk.nsheets)
try:
 sh = bk.sheet_by_name("sheet1")
except:
 print("no sheet in", fname ,"named sheet1")
#获取行数
nrows = sh.nrows
number_of_students=nrows-4
#获取列数
ncols = sh.ncols
print("(nrows , ncols) is ( ", nrows,',', ncols, " )")

# 设置常用字符
str1="|"
str2=":"
str3="--"
Title1=""
Title2=""
Title3=""

#形成表格的抬头
for j in range(0,ncols):
	Title1=Title1+ str1 + str(sh.cell_value(1,j))
	Title2=Title2+ str1 + str2+str3+str2
	Title3=Title3+ str1

Title1=Title1+str1
Title2=Title2+str1
Title3=Title3+str1

# 读取表格的记录（数据）
Contents=[] # 所有行的内容

for row in range(2,nrows-2):   # 最后两行是 Max 和 Min，不输出到Markdown
  content=""  # 当前行的内容
  for col in range(0,ncols):
    if col==1: # 处理博客，带超级链接
      name=sh.cell_value(row,1)
      link= sh.hyperlink_map.get((row, 1))
      url = 'No URL' if link is None else link.url_or_path
      content= content + str1 + "["+name+"]("+url+")"
    elif col==2: # 处理git，带超级链接
      name=sh.cell_value(row,2)
      link= sh.hyperlink_map.get((row, 2))
      url = 'No URL' if link is None else link.url_or_path
      content= content + str1 + "["+name+"]("+url+")"
    else:
      content= content + str1 + str(sh.cell_value(row,col))
  content= content + str1
  Contents.append(content)

# 将Title1、Title2、Title3、Contents列表中的内容 写到模版文件中去

## 先将模版文件内容读到临时串中
#encoding= 'utf8'
f = open(fname_Template_md, 'r',encoding= 'utf8')  #指定文件的编码格式
try:
	str_template=f.read()
except Exception as e:
	str_template="False"
	raise
else:
	pass
finally:
	f.close()


## 新建一个文件
fp = open(fname_output_md,'w',encoding= 'utf8')  # 直接打开一个文件，如果文件不存在则创建文件
fp.write(str_template)
fp.writelines("\n")
fp.writelines(Title1)
fp.writelines("\n")
fp.writelines(Title2)
fp.writelines("\n")
fp.writelines(Title3)
fp.writelines("\n")
for i in range(0,len(Contents)):
  fp.writelines(Contents[i])
  fp.writelines("\n")
fp.close()

# 更新一下第二个 sheet 内容 
## step1 ：放在字典中，score={}
## step2 ：排序放入列表中，score_list
## step3 ：写入 sheet2 
# step1
score={}
for row in range(2,nrows-2):   # 最后两行是 Max 和 Min，不输出到Markdown
  name_no =sh.cell_value(row,0) +"_"+ sh.cell_value(row,1)
  score_value= sh.cell_value(row,ncols-1)
  score[name_no]=score_value

# for name in score:
#   print(name,score[name])

# step 2
score_list= sorted(score.items(), key=lambda v: v[1], reverse=True)  # reverse=True 为降序

# step 3: 写文件
# bkw is opened 
# bkw=copy(bk)
# print(bk)

# shxrange = range(bkw.nsheets)
# try:
#  sh = bk.sheet_by_name("sheet2")
# except:
#  print("no sheet in", fname ,"named sheet2")

# sh.write(1,1,2)

for item in score_list[:number_of_students]:
  name=re.split(r'_',item[0])
  print(name[0],name[1],item[1])



  
  # content=""  # 当前行的内容
  # for col in range(0,ncols):
  #   if col==1: # 处理博客，带超级链接
  #     name=sh.cell_value(row,1)
  #     link= sh.hyperlink_map.get((row, 1))
  #     url = 'No URL' if link is None else link.url_or_path
  #     content= content + str1 + "["+name+"]("+url+")"
  #   elif col==2: # 处理git，带超级链接
  #     name=sh.cell_value(row,2)
  #     link= sh.hyperlink_map.get((row, 2))
  #     url = 'No URL' if link is None else link.url_or_path
  #     content= content + str1 + "["+name+"]("+url+")"
  #   else:
  #     content= content + str1 + str(sh.cell_value(row,col))
  # content= content + str1
  # Contents.append(content)

